import mysql.connector
import json
import os
import sqlite3
import re
import chardet

def detect_encoding(file_path):
    """检测文件编码"""
    with open(file_path, 'rb') as f:
        raw_data = f.read(10000)
        result = chardet.detect(raw_data)
        return result['encoding']

def sanitize_name(name):
    """清理名称，移除特殊字符"""
    return re.sub(r'[^\w]', '_', name)

def extract_number_from_string(text):
    """从字符串中提取数字"""
    if text is None:
        return None
    if isinstance(text, (int, float)):
        return text
    # 查找字符串中的数字
    numbers = re.findall(r'\d+', str(text))
    if numbers:
        return int(numbers[0])
    return 0

def convert_yes_no_to_boolean(text):
    """将中文的是/否转换为布尔值"""
    if text is None:
        return None
    text_str = str(text).strip()
    if text_str in ['是', 'yes', 'true', '1', 'True']:
        return 1
    elif text_str in ['否', 'no', 'false', '0', 'False']:
        return 0
    return None

def clean_data_value(value, mysql_type, column_name=None):
    """清理数据值，处理特殊字符和类型转换"""
    if value is None:
        return None
    
    # 处理特殊字符串值
    if isinstance(value, str):
        value = value.strip()
        if value.upper() in ['NULL', 'N/A', 'NA', '']:
            return None
        if value.upper() == 'T':  # 处理天气数据中的'T'值
            if 'INT' in mysql_type:
                return 0
            elif 'FLOAT' in mysql_type or 'DOUBLE' in mysql_type:
                return 0.0
    
    # 根据列名进行特殊处理
    if column_name:
        col_name_lower = column_name.lower()
        
        # 处理是否胜利列
        if any(keyword in col_name_lower for keyword in ['是否', '胜利', 'win', 'success']):
            bool_value = convert_yes_no_to_boolean(value)
            if bool_value is not None:
                return bool_value
        
        # 处理ID列，提取数字
        if any(keyword in col_name_lower for keyword in ['id', '编号', '序号']):
            if isinstance(value, str) and not value.isdigit():
                return extract_number_from_string(value)
    
    # 处理空字符串转数字
    if value == '' and ('INT' in mysql_type or 'FLOAT' in mysql_type or 'DOUBLE' in mysql_type):
        return 0
    
    # 处理超出范围的数值
    if isinstance(value, (int, float)) and 'INT' in mysql_type:
        if value > 2147483647:  # MySQL INT最大值
            return 2147483647
        elif value < -2147483648:  # MySQL INT最小值
            return -2147483648
    
    return value

def fix_encoding(text):
    """修复编码问题"""
    if text is None:
        return None
    
    if isinstance(text, bytes):
        # 尝试多种编码
        encodings = ['utf-8', 'latin-1', 'cp1252', 'iso-8859-1', 'gbk', 'gb2312']
        for encoding in encodings:
            try:
                return text.decode(encoding)
            except UnicodeDecodeError:
                continue
        # 如果所有编码都失败，使用忽略错误的方式
        return text.decode('utf-8', errors='ignore')
    
    elif isinstance(text, str):
        # 如果已经是字符串但包含乱码，尝试修复
        if '��' in text:
            # 尝试重新编码和解码
            try:
                # 假设原始是latin-1，重新编码为bytes然后解码为utf-8
                fixed = text.encode('latin-1').decode('utf-8')
                return fixed
            except:
                # 如果失败，返回原字符串但清理无效字符
                return re.sub(r'[^\x00-\x7F]+', '', text)
    
    return text

def get_mysql_type_for_column(col_name, col_type, sample_data=None):
    """根据列名和类型确定MySQL类型"""
    col_type_upper = col_type.upper() if col_type else 'TEXT'
    col_name_lower = col_name.lower()
    
    # 根据列名进行智能类型推断
    if any(keyword in col_name_lower for keyword in ['是否', 'enable', 'active']):
        return 'BOOLEAN'
    elif any(keyword in col_name_lower for keyword in ['id', '编号', '序号']):
        return 'INT'
    elif any(keyword in col_name_lower for keyword in ['时间', '日期', 'date', 'time']):
        return 'DATETIME'
    elif any(keyword in col_name_lower for keyword in ['价格', '金额', '分数', 'score', 'price', 'amount', 'cost']):
        # 特别处理网球比分
        if 'score' in col_name_lower and sample_data:
            # 检查样本数据是否包含网球比分格式
            if isinstance(sample_data, str) and re.match(r'\d+-\d+\s+\d+-\d+', sample_data):
                return 'VARCHAR(50)'
        return 'DECIMAL(10,2)'
    elif 'age' in col_name_lower:
        return 'FLOAT'
    elif 'INT' in col_type_upper:
        return 'INT'
    elif 'REAL' in col_type_upper or 'FLOAT' in col_type_upper or 'DOUBLE' in col_type_upper:
        return 'FLOAT'
    elif 'DECIMAL' in col_type_upper or 'NUMERIC' in col_type_upper:
        return 'DECIMAL(10,2)'
    elif 'BOOL' in col_type_upper:
        return 'BOOLEAN'
    elif 'DATE' in col_type_upper or 'TIME' in col_type_upper:
        return 'DATETIME'
    elif 'BLOB' in col_type_upper:
        return 'LONGBLOB'
    else:
        # 对于文本类型，根据列名判断是否需要更长的长度
        if any(keyword in col_name_lower for keyword in ['描述', '内容', '说明', 'description', 'content', 'comment', 'note']):
            return 'LONGTEXT'
        elif any(keyword in col_name_lower for keyword in ['名称', '标题', '姓名', 'name', 'title', 'email', 'phone', 'zip_code', 'hand', 'ioc', 'entry']):
            return 'VARCHAR(255)'
        elif any(keyword in col_name_lower for keyword in ['score', 'round', 'surface', 'level']):
            return 'VARCHAR(50)'
        else:
            return 'TEXT'

def import_sqlite_to_mysql(sqlite_path, mysql_conn, db_name):
    """将SQLite数据库导入到MySQL"""
    try:
        # 检测文件编码
        file_encoding = detect_encoding(sqlite_path)
        print(f"    检测到文件编码: {file_encoding}")
        
        # 连接SQLite数据库
        sqlite_conn = sqlite3.connect(sqlite_path)
        
        # 设置文本处理函数来处理编码问题
        sqlite_conn.text_factory = lambda x: x.decode('utf-8', errors='replace') if isinstance(x, bytes) else x
        
        sqlite_cursor = sqlite_conn.cursor()
        
        mysql_cursor = mysql_conn.cursor()
        
        # 创建MySQL数据库
        mysql_cursor.execute(f"CREATE DATABASE IF NOT EXISTS `{db_name}` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci")
        mysql_cursor.execute(f"USE `{db_name}`")
        
        print(f"📁 导入SQLite数据库: {os.path.basename(sqlite_path)} -> {db_name}")
        
        # 获取SQLite中的所有表
        sqlite_cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
        tables = sqlite_cursor.fetchall()
        
        for table_info in tables:
            table_name = table_info[0]
            
            # 获取表结构
            sqlite_cursor.execute(f"PRAGMA table_info(`{table_name}`)")
            columns = sqlite_cursor.fetchall()
            
            # 先获取一些样本数据用于智能类型推断
            sample_data = {}
            sqlite_cursor.execute(f"SELECT * FROM `{table_name}` LIMIT 5")
            sample_rows = sqlite_cursor.fetchall()
            if sample_rows:
                col_names = [col[1] for col in columns]
                for i, col_name in enumerate(col_names):
                    if i < len(sample_rows[0]):
                        sample_data[col_name] = sample_rows[0][i]
            
            # 构建CREATE TABLE语句
            create_columns = []
            column_types = {}  # 存储列名和对应的MySQL类型
            
            for col in columns:
                col_id, col_name, col_type, not_null, default_val, pk = col
                
                # 智能类型映射，使用样本数据进行更精确的推断
                sample_value = sample_data.get(col_name)
                mysql_type = get_mysql_type_for_column(col_name, col_type, sample_value)
                
                create_columns.append(f"`{col_name}` {mysql_type}")
                column_types[col_name] = mysql_type
            
            create_table_sql = f"CREATE TABLE IF NOT EXISTS `{table_name}` ({', '.join(create_columns)}) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"
            
            try:
                mysql_cursor.execute(create_table_sql)
                
                # 导入数据
                sqlite_cursor.execute(f"SELECT * FROM `{table_name}`")
                rows = sqlite_cursor.fetchall()
                
                if rows:
                    # 获取列名
                    col_names = [col[1] for col in columns]
                    placeholders = ', '.join(['%s'] * len(col_names))
                    
                    insert_sql = f"INSERT INTO `{table_name}` ({', '.join([f'`{name}`' for name in col_names])}) VALUES ({placeholders})"
                    
                    success_count = 0
                    error_count = 0
                    error_samples = []
                    
                    for row in rows:
                        try:
                            # 清理数据并修复编码
                            cleaned_row = []
                            for i, value in enumerate(row):
                                col_name = col_names[i]
                                mysql_type = column_types.get(col_name, 'TEXT')
                                
                                # 修复编码问题
                                if isinstance(value, (str, bytes)):
                                    value = fix_encoding(value)
                                
                                cleaned_value = clean_data_value(value, mysql_type, col_name)
                                cleaned_row.append(cleaned_value)
                            
                            mysql_cursor.execute(insert_sql, cleaned_row)
                            success_count += 1
                        except Exception as row_error:
                            error_count += 1
                            # 收集错误样本用于调试
                            if error_count <= 3:
                                error_samples.append({
                                    'error': str(row_error),
                                    'data': row,
                                    'table': table_name
                                })
                            continue
                    
                    # 显示错误信息
                    if error_samples:
                        print(f"  ⚠️  表 {table_name}: {success_count} 行成功, {error_count} 行失败")
                        for sample in error_samples[:2]:  # 只显示前2个错误样本
                            print(f"    错误示例: {sample['error']}")
                            # 显示有问题的数据
                            problematic_data = []
                            for i, val in enumerate(sample['data']):
                                col_name = col_names[i]
                                mysql_type = column_types.get(col_name, 'TEXT')
                                problematic_data.append(f"{col_name}({mysql_type})={repr(val)}")
                            print(f"    问题数据: {', '.join(problematic_data)}")
                    else:
                        print(f"  ✅ 表 {table_name}: {success_count} 行数据成功, {error_count} 行失败")
                else:
                    print(f"  ✅ 表 {table_name}: 0 行数据")
                    
            except Exception as e:
                print(f"  ❌ 表 {table_name} 导入失败: {e}")
                # 打印更多调试信息
                import traceback
                print(f"    详细错误: {traceback.format_exc()}")
        
        sqlite_conn.close()
        mysql_conn.commit()
        return True
        
    except Exception as e:
        print(f"❌ 导入SQLite数据库失败: {e}")
        import traceback
        print(f"详细错误: {traceback.format_exc()}")
        return False

def analyze_chase_tables_structure(tables_data):
    """分析chase_tables.json的结构"""
    print("🔍 分析chase_tables.json结构...")
    
    if isinstance(tables_data, dict):
        print("  ✅ 结构: 字典格式")
        print(f"  包含 {len(tables_data)} 个数据库")
        for db_id, tables in list(tables_data.items())[:3]:  # 只显示前3个
            print(f"    - {db_id}: {len(tables)} 个表")
        return "dict"
    
    elif isinstance(tables_data, list):
        print("  ✅ 结构: 列表格式")
        print(f"  包含 {len(tables_data)} 个表定义")
        
        # 检查列表中的元素类型
        if tables_data and isinstance(tables_data[0], dict):
            sample_item = tables_data[0]
            print(f"  示例表结构键: {list(sample_item.keys())}")
            
            # 尝试找出可能的数据库ID字段
            possible_db_id_fields = ['db_id', 'database_id', 'db_name', 'database']
            db_id_field = None
            for field in possible_db_id_fields:
                if field in sample_item:
                    db_id_field = field
                    break
            
            if db_id_field:
                print(f"  使用 '{db_id_field}' 作为数据库ID字段")
                # 按数据库ID分组
                grouped_data = {}
                for table_def in tables_data:
                    db_id = table_def.get(db_id_field, 'unknown_database')
                    if db_id not in grouped_data:
                        grouped_data[db_id] = []
                    grouped_data[db_id].append(table_def)
                print(f"  分组为 {len(grouped_data)} 个数据库")
                return ("grouped_list", grouped_data)
            else:
                print("  ⚠️  未找到数据库ID字段，将所有表放在一个数据库中")
                return ("flat_list", tables_data)
        
        return "unknown_list"
    
    else:
        print(f"  ❌ 未知结构类型: {type(tables_data)}")
        return "unknown"

def import_chase_tables(mysql_conn, tables_file_path):
    """根据chase_tables.json创建数据库结构"""
    try:
        with open(tables_file_path, 'r', encoding='utf-8') as f:
            tables_data = json.load(f)
        
        cursor = mysql_conn.cursor()
        
        print("🗃️ 根据chase_tables.json创建数据库结构...")
        
        # 分析数据结构
        structure_type = analyze_chase_tables_structure(tables_data)
        
        if structure_type == "dict":
            # 原始字典格式处理
            for db_id, db_tables in tables_data.items():
                print(f"\n📁 创建数据库: {db_id}")
                # 创建数据库
                cursor.execute(f"CREATE DATABASE IF NOT EXISTS `{db_id}` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci")
                cursor.execute(f"USE `{db_id}`")
                
                # 为每个表创建结构
                for table_info in db_tables:
                    _create_table_from_info(cursor, table_info)
        
        elif isinstance(structure_type, tuple) and structure_type[0] == "grouped_list":
            # 分组列表格式处理
            grouped_data = structure_type[1]
            for db_id, db_tables in grouped_data.items():
                print(f"\n📁 创建数据库: {db_id}")
                # 创建数据库
                cursor.execute(f"CREATE DATABASE IF NOT EXISTS `{db_id}` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci")
                cursor.execute(f"USE `{db_id}`")
                
                # 为每个表创建结构
                for table_info in db_tables:
                    _create_table_from_info(cursor, table_info)
        
        elif isinstance(structure_type, tuple) and structure_type[0] == "flat_list":
            # 平铺列表格式处理 - 将所有表放在一个数据库中
            db_id = "chase_tables"
            print(f"\n📁 创建数据库: {db_id}")
            cursor.execute(f"CREATE DATABASE IF NOT EXISTS `{db_id}` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci")
            cursor.execute(f"USE `{db_id}`")
            
            # 为每个表创建结构
            for table_info in tables_data:
                _create_table_from_info(cursor, table_info)
        
        else:
            print(f"❌ 不支持的chase_tables.json结构: {structure_type}")
            return False
        
        mysql_conn.commit()
        cursor.close()
        print("\n🎉 chase_tables数据库结构创建完成!")
        return True
        
    except Exception as e:
        print(f"❌ 创建chase_tables数据库结构时出错: {e}")
        import traceback
        print(f"详细错误: {traceback.format_exc()}")
        return False

def _create_table_from_info(cursor, table_info):
    """根据表信息创建表"""
    try:
        # 处理不同的表信息结构
        if 'table_name' in table_info:
            table_name = table_info['table_name']
        elif 'name' in table_info:
            table_name = table_info['name']
        else:
            print(f"  ❌ 表信息缺少表名: {table_info}")
            return
        
        # 获取列信息
        if 'column_names' in table_info:
            column_names = table_info['column_names']
        elif 'columns' in table_info:
            column_names = table_info['columns']
        else:
            print(f"  ❌ 表 {table_name} 缺少列信息")
            return
        
        column_types = table_info.get('column_types', [])
        
        # 构建CREATE TABLE语句
        create_table_sql = f"CREATE TABLE IF NOT EXISTS `{table_name}` ("
        
        # 添加列定义
        columns_def = []
        for i, column in enumerate(column_names):
            # 处理不同的列名格式
            if isinstance(column, list) and len(column) > 1:
                col_name = column[1]
            elif isinstance(column, dict):
                col_name = column.get('name', f'column_{i}')
            else:
                col_name = str(column)
            
            col_name = sanitize_name(col_name)
            
            # 确定列类型
            if i < len(column_types):
                col_type = column_types[i]
                # 简化类型映射
                if 'int' in col_type.lower():
                    mysql_type = "INT"
                elif 'float' in col_type.lower() or 'double' in col_type.lower():
                    mysql_type = "FLOAT"
                elif 'decimal' in col_type.lower() or 'numeric' in col_type.lower():
                    mysql_type = "DECIMAL(10,2)"
                elif 'bool' in col_type.lower():
                    mysql_type = "BOOLEAN"
                else:
                    mysql_type = "TEXT"
            else:
                # 使用智能类型推断
                mysql_type = get_mysql_type_for_column(col_name, '')
            
            columns_def.append(f"`{col_name}` {mysql_type}")
        
        create_table_sql += ", ".join(columns_def)
        create_table_sql += ") CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"
        
        # 执行创建表
        cursor.execute(create_table_sql)
        print(f"  ✅ 创建表: {table_name}")
        
    except Exception as e:
        print(f"  ❌ 创建表失败: {e}")

def import_chase_data(mysql_conn, data_file_path, data_type):
    """导入chase数据文件（dev/test/train）"""
    try:
        with open(data_file_path, 'r', encoding='utf-8') as f:
            data = json.load(f)
        
        cursor = mysql_conn.cursor()
        
        print(f"📥 导入{data_type}数据: {os.path.basename(data_file_path)}")
        
        # 创建数据存储表
        cursor.execute(f"CREATE DATABASE IF NOT EXISTS `chase_data` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci")
        cursor.execute("USE `chase_data`")
        
        table_name = f"chase_{data_type}"
        create_table_sql = f"""
        CREATE TABLE IF NOT EXISTS `{table_name}` (
            id INT AUTO_INCREMENT PRIMARY KEY,
            db_id VARCHAR(255),
            question TEXT,
            sql_query JSON,
            interaction_data JSON,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
        """
        cursor.execute(create_table_sql)
        
        success_count = 0
        error_count = 0
        for record in data:
            try:
                insert_sql = f"""
                INSERT INTO `{table_name}` (db_id, question, sql_query, interaction_data)
                VALUES (%s, %s, %s, %s)
                """
                
                # 清理数据
                db_id = clean_data_value(record.get('db_id'), 'VARCHAR(255)')
                question = clean_data_value(record.get('question'), 'TEXT')
                sql_query = json.dumps(record.get('sql', {}), ensure_ascii=False)
                interaction_data = json.dumps(record.get('interaction', []), ensure_ascii=False)
                
                cursor.execute(insert_sql, (db_id, question, sql_query, interaction_data))
                success_count += 1
                
            except Exception as e:
                error_count += 1
                if error_count <= 3:  # 只显示前几个错误
                    print(f"  记录插入失败: {e}")
                continue
        
        mysql_conn.commit()
        cursor.close()
        print(f"  ✅ {data_type}数据导入完成: {success_count}/{len(data)} 条记录成功, {error_count} 条失败")
        return True
        
    except Exception as e:
        print(f"❌ 导入{data_type}数据时出错: {e}")
        return False

def main():
    # 远程MySQL连接配置
    mysql_config = {
        'host': '192.168.0.162',
        'port': 3310,
        'user': 'root',
        'password': '123456',
        'charset': 'utf8mb4',
        'use_unicode': True,
        'collation': 'utf8mb4_unicode_ci'
    }
    
    base_path = r'C:\Users\shi\Desktop\实习'
    database_path = os.path.join(base_path, 'database')
    
    try:
        # 连接远程MySQL
        print(f"🔗 连接到远程MySQL服务器: {mysql_config['host']}:{mysql_config['port']}")
        conn = mysql.connector.connect(**mysql_config)
        
        if conn.is_connected():
            print("✅ 远程MySQL连接成功!")
            
            # 设置字符集
            cursor = conn.cursor()
            cursor.execute("SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci")
            cursor.close()
            
            # 1. 导入SQLite数据库文件
            if os.path.exists(database_path):
                print(f"\n{'='*50}")
                print("开始导入SQLite数据库文件...")
                print(f"{'='*50}")
                
                sqlite_files = [f for f in os.listdir(database_path) if f.endswith('.sqlite')]
                print(f"找到 {len(sqlite_files)} 个SQLite文件")
                
                for sqlite_file in sqlite_files:
                    sqlite_path = os.path.join(database_path, sqlite_file)
                    db_name = sanitize_name(os.path.splitext(sqlite_file)[0])
                    
                    # 特别处理有编码问题的数据库
                    if sqlite_file == 'wta_1.sqlite':
                        print(f"🔧 特殊处理网球数据库: {sqlite_file}")
                    elif sqlite_file == '世界篮球国家队.sqlite':
                        print(f"🔧 特殊处理篮球数据库: {sqlite_file}")
                    
                    import_sqlite_to_mysql(sqlite_path, conn, db_name)
            
            # 2. 导入chase_tables.json数据库结构
            print(f"\n{'='*50}")
            print("开始导入chase_tables.json数据库结构...")
            print(f"{'='*50}")
            
            tables_file_path = os.path.join(base_path, 'chase_tables.json')
            if os.path.exists(tables_file_path):
                import_chase_tables(conn, tables_file_path)
            else:
                print(f"❌ chase_tables.json文件不存在: {tables_file_path}")
            
            # 3. 导入chase数据文件
            print(f"\n{'='*50}")
            print("开始导入chase数据文件...")
            print(f"{'='*50}")
            
            chase_files = [
                ('chase_dev.json', 'dev'),
                ('chase_test.json', 'test'), 
                ('chase_train.json', 'train')
            ]
            
            for filename, data_type in chase_files:
                file_path = os.path.join(base_path, filename)
                if os.path.exists(file_path):
                    import_chase_data(conn, file_path, data_type)
                else:
                    print(f"❌ {filename}文件不存在")
            
            # 显示导入统计
            print(f"\n{'='*50}")
            print("导入完成统计")
            print(f"{'='*50}")
            
            cursor = conn.cursor()
            cursor.execute("SHOW DATABASES")
            databases = [db[0] for db in cursor.fetchall() if not db[0] in ['information_schema', 'mysql', 'performance_schema', 'sys']]
            
            print(f"📊 总共创建了 {len(databases)} 个数据库:")
            
            for db in databases:
                cursor.execute(f"USE `{db}`")
                cursor.execute("SHOW TABLES")
                tables = [table[0] for table in cursor.fetchall()]
                print(f"  📁 {db}: {len(tables)} 个表")
                
                # 显示每个表的数据量
                for table in tables:
                    try:
                        cursor.execute(f"SELECT COUNT(*) FROM `{table}`")
                        count = cursor.fetchone()[0]
                        print(f"    └─ {table}: {count} 行数据")
                    except:
                        print(f"    └─ {table}: 结构表")
            
            cursor.close()
            conn.close()
            print(f"\n🎉 所有数据导入完成!")
            
    except mysql.connector.Error as e:
        print(f"❌ MySQL连接错误: {e}")

if __name__ == "__main__":
    # 安装所需依赖
    try:
        import chardet
    except ImportError:
        print("安装chardet库以检测文件编码...")
        import subprocess
        import sys
        subprocess.check_call([sys.executable, "-m", "pip", "install", "chardet"])
        import chardet
    
    main()